package com.code.day03;


import com.mysql.cj.jdbc.Driver;
import org.junit.Test;

import java.sql.*;

public class Demo01JDBC {

    @Test
    public void insert() throws SQLException, ClassNotFoundException {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");


        // 连接数据库
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "test";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        //System.out.println(conn);

        // 执行语句
        Statement stmt = conn.createStatement();
        String sql = "insert into t_user (username,password) values ('jerry','222');";
        stmt.execute(sql);


        //关闭连接资源
        stmt.close();
        conn.close();

    }


    @Test
    public void update() throws SQLException, ClassNotFoundException {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");


        // 连接数据库
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "test";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        //System.out.println(conn);

        // 执行语句
        Statement stmt = conn.createStatement();
        String sql = "update t_user set password='333' where username='jerry';";
        stmt.execute(sql);


        //关闭连接资源
        stmt.close();
        conn.close();

    }

    @Test
    public void delete() throws SQLException, ClassNotFoundException {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");


        // 连接数据库
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "test";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        //System.out.println(conn);

        // 执行语句
        Statement stmt = conn.createStatement();
        String sql = "delete from t_user  where username='jerry';";
        stmt.execute(sql);


        //关闭连接资源
        stmt.close();
        conn.close();

    }

    @Test
    public void select() throws SQLException, ClassNotFoundException {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");


        // 连接数据库
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "test";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        //System.out.println(conn);

        // 执行语句
        Statement stmt = conn.createStatement();
        String sql = "select * from t_user ;";
        ResultSet rex =  stmt.executeQuery(sql);
        while(rex.next()){
            int id = rex.getInt("id");
            String username = rex.getString("username");
            String pwd = rex.getString("password");

            System.out.println(id + "," + username + "," + pwd);
        }


        //关闭连接资源
        rex.close();
        stmt.close();
        conn.close();

    }

}
